﻿using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace XLB.ExtensionMethod.DB
{

    /// <summary>
    /// mysql数据操作类
    /// </summary>
    public class MysqlDBHelper
    {
        /// <summary>
        /// 定义连接字符串
        /// </summary>
        static string connString = "Server=127.0.0.1;User Id=root;Password=root;DataBase=Webshop;charset=utf8;";
        public MysqlDBHelper()
        {

        }

        /// <summary>
        /// 执行数据库查询操作，返回数据集DataSet
        /// </summary>
        /// <param name="sqlText"></param>
        /// <returns></returns>
        public static DataSet GetDataSet(string sqlText)
        {
            DataSet ds = new DataSet();
            using (MySqlConnection conn = new MySqlConnection(connString))
            {
                if (conn.State == ConnectionState.Closed)
                    conn.Open();
                MySqlCommand comm = new MySqlCommand(sqlText, conn);
                MySqlDataAdapter da = new MySqlDataAdapter(comm);
                da.Fill(ds);
            }
            return ds;
        }

        /// <summary>
        /// 执行数据库查询操作，返回须向只读数据集DataReader
        /// </summary>
        /// <param name="sqlText"></param>
        /// <returns></returns>
        public static MySqlDataReader GetDataReader(string sqlText)
        {
            MySqlConnection conn = new MySqlConnection(connString);
            conn.Open();
            MySqlCommand comm = new MySqlCommand(sqlText, conn);
            MySqlDataReader dr = comm.ExecuteReader(CommandBehavior.CloseConnection);
            return dr;
        }

        /// <summary>
        /// 执行数据库增、删、改操作的方法，返回影响的行数
        /// </summary>
        /// <param name="sqlText"></param>
        /// <returns></returns>
        public static int GetExecuteNonQuery(string sqlText)
        {
            int i = 0;
            using (MySqlConnection conn = new MySqlConnection(connString))
            {
                conn.Open();
                MySqlCommand comm = new MySqlCommand(sqlText, conn);
                i = comm.ExecuteNonQuery();
            }
            return i;
        }

        /// <summary>
        /// 执行数据库增、删、改操作的方法，返回影响的行数
        /// </summary>
        /// <param name="sqlText"></param>
        /// <param name="commType"></param>
        /// <param name="p"></param>
        /// <returns></returns>
        public static int GetExecuteNonQuery(string sqlText, CommandType commType, MySqlParameter[] p)
        {
            int i = 0;
            using (MySqlConnection conn = new MySqlConnection(connString))
            {
                conn.Open();
                MySqlCommand comm = new MySqlCommand(sqlText, conn);
                comm.CommandType = commType;                
                comm.Parameters.AddRange(p);
                i = comm.ExecuteNonQuery();
            }
            return i;
        }

        /// <summary>
        /// 执行数据库查询操作,返回结果第一行第一列object对象
        /// </summary>
        /// <param name="sqlText"></param>
        /// <returns></returns>
        public static object GetExecuteScalar(string sqlText)
        {
            object o;
            using (MySqlConnection conn = new MySqlConnection(connString))
            {
                conn.Open();
                MySqlCommand comm = new MySqlCommand(sqlText, conn);
                o = comm.ExecuteScalar();
            }
            return o;
        }

        /// <summary>
        /// 执行数据库查询操作,返回结果第一行第一列object对象
        /// </summary>
        /// <param name="sqlText"></param>
        /// <param name="commType"></param>
        /// <param name="p"></param>
        /// <returns></returns>
        public static object GetExecuteScalar(string sqlText, CommandType commType, MySqlParameter[] p)
        {
            object o;
            using (MySqlConnection conn = new MySqlConnection(connString))
            {
                conn.Open();
                MySqlCommand comm = new MySqlCommand(sqlText, conn);
                comm.CommandType = commType;
                comm.Parameters.AddRange(p);
                o = comm.ExecuteScalar();
            }
            return o;
        }
    }
}
